package com.md.notes.model.db.service;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.md.notes.model.db.DatabaseHelper;
import com.md.notes.model.entity.Note;
import com.md.notes.util.DateUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 数据访问服务类
 * 提供对Note表的增删改查操作
 */
public class DaoService {
    private DatabaseHelper dbHelper;
    private SQLiteDatabase db;

    public DaoService(Context context) {
        dbHelper = new DatabaseHelper(context);
    }

    /**
     * 插入一条笔记
     */
    public long insertNote(Note note) {
        db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("title", note.getTitle());
        values.put("createDate", DateUtils.formatToLong(note.getCreateDate()));
        values.put("updateDate", System.currentTimeMillis());
        values.put("specNo", note.getSpecNo());
        values.put("content", note.getContent());
        values.put("appNo", note.getAppNo());
        
        long id = db.insert(DatabaseHelper.TABLE_NOTE, null, values);
        db.close();
        return id;
    }

    /**
     * 删除笔记
     */
    public int deleteNote(String specNo) {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DatabaseHelper.TABLE_NOTE, "specNo = ?", 
                             new String[]{specNo});
        db.close();
        return result;
    }

    /**
     * 更新笔记
     */
    public int updateNote(Note note) {
        db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("title", note.getTitle());
        values.put("createDate", note.getCreateDate());
        values.put("updateDate", System.currentTimeMillis());
        values.put("content", note.getContent());
        values.put("appNo", note.getAppNo());
        
        int result = db.update(DatabaseHelper.TABLE_NOTE, values, 
                             "specNo = ?", new String[]{note.getSpecNo()});
        db.close();
        return result;
    }

    /**
     * 查询所有笔记，按创建日期降序排序
     */
    public List<Note> getAllNotes() {
        List<Note> noteList = new ArrayList<>();
        db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query(DatabaseHelper.TABLE_NOTE, null, 
                               null, null, null, null, 
                               "createDate DESC");
        
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                note.setCreateDate(cursor.getString(cursor.getColumnIndex("createDate")));
                note.setSpecNo(cursor.getString(cursor.getColumnIndex("specNo")));
                note.setContent(cursor.getString(cursor.getColumnIndex("content")));
                note.setAppNo(cursor.getString(cursor.getColumnIndex("appNo")));
                noteList.add(note);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return noteList;
    }

    /**
     * 获取第一条笔记（按创建日期降序排序后的第一条）
     */
    public Note getFirstNote() {
        db = dbHelper.getReadableDatabase();
        Note note = null;
        Cursor cursor = db.query(DatabaseHelper.TABLE_NOTE, null, 
                               null, null, null, null, 
                               "createDate DESC", "1");
        
        if (cursor.moveToFirst()) {
            note = new Note();
            note.setTitle(cursor.getString(cursor.getColumnIndex("title")));
            note.setCreateDate(cursor.getString(cursor.getColumnIndex("createDate")));
            note.setSpecNo(cursor.getString(cursor.getColumnIndex("specNo")));
            note.setContent(cursor.getString(cursor.getColumnIndex("content")));
            note.setAppNo(cursor.getString(cursor.getColumnIndex("appNo")));
        }
        cursor.close();
        db.close();
        return note;
    }

    /**
     * 清除note表的所有数据
     * @return 返回删除的行数
     */
    public int clearAllNotes() {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DatabaseHelper.TABLE_NOTE, null, null);
        db.close();
        return result;
    }

    /**
     * 根据标题精确查询笔记
     * @param title 笔记标题
     * @return 返回匹配的笔记，如果没有找到返回null
     */
    public Note getNoteByExactTitle(String title) {
        db = dbHelper.getReadableDatabase();
        Note note = null;
        Cursor cursor = db.query(DatabaseHelper.TABLE_NOTE, null,
                "title = ?", new String[]{title},
                null, null, null);

        if (cursor.moveToFirst()) {
            note = new Note();
            note.setTitle(cursor.getString(cursor.getColumnIndex("title")));
            note.setCreateDate(cursor.getString(cursor.getColumnIndex("createDate")));
            note.setSpecNo(cursor.getString(cursor.getColumnIndex("specNo")));
            note.setContent(cursor.getString(cursor.getColumnIndex("content")));
            note.setAppNo(cursor.getString(cursor.getColumnIndex("appNo")));
        }
        cursor.close();
        db.close();
        return note;
    }

    /**
     * 根据标题模糊查询笔记列表
     * @param titleKeyword 标题关键词
     * @return 返回包含关键词的笔记列表
     */
    public List<Note> searchNotesByTitle(String titleKeyword) {
        List<Note> noteList = new ArrayList<>();
        db = dbHelper.getReadableDatabase();
        // 使用LIKE进行模糊查询，%表示匹配任意字符
        String selection = "title LIKE ?";
        String[] selectionArgs = new String[]{"%" + titleKeyword + "%"};
        
        Cursor cursor = db.query(DatabaseHelper.TABLE_NOTE, null,
                selection, selectionArgs,
                null, null, "createDate DESC");

        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                note.setCreateDate(cursor.getString(cursor.getColumnIndex("createDate")));
                note.setSpecNo(cursor.getString(cursor.getColumnIndex("specNo")));
                note.setContent(cursor.getString(cursor.getColumnIndex("content")));
                note.setAppNo(cursor.getString(cursor.getColumnIndex("appNo")));
                noteList.add(note);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return noteList;
    }
}
